﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using Entities;
using System.Data.SqlClient;
using DAOExceptions;

namespace SQLDao
{
    public class SQL_User_DAO : IUserDAO
    {
        public int ActivateAccount(string key)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@key", key);
            command.CommandText = "SELECT [user_id], [key] FROM [AccountActivation] WHERE [key]=@key";
            int uid = -1;
            try
            {
                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        uid = (int)reader["user_id"];
                    }
                    else
                    {
                        return -1;
                    }
                }
                command.CommandText = "DELETE FROM [AccountActivation] WHERE [key]=@key";
                command.ExecuteNonQuery();
                return uid;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while activating account");
            }
        }

        public User LoadUser(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = "SELECT [id], [login], [password], [email], [last_quota]  FROM [Users] WHERE [id]=@id";
            User user = new User();
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        user.ID = (int)reader["id"];
                        user.Login = (string)reader["login"];
                        user.Password = (string)reader["password"];
                        user.Email = (string)reader["email"];
                        user.LastQuota = (int)reader["last_quota"];
                    }
                    else
                    {
                        return user;
                    }
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading user");
            }
            IRoleDAO rdao = new SQL_Role_DAO();
            user.Roles = rdao.GetRolesForUser(user.ID);

            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            user.HostedFiles = hfdao.GetHostedFilesForUser(user.ID);

            user.State = EntityState.Unchanged;
            return user;
        }

        public bool IsValidAuth(User auth_user)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@login", auth_user.Login);
            command.Parameters.AddWithValue("@password", auth_user.Password);
            command.CommandText = "SELECT [id] FROM [Users] WHERE [login]=@login AND [password]=@password";
            int result = -1;
            try
            {
                var res = command.ExecuteScalar().ToString();
                int.TryParse(res, out result);
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking user auth params");
            }

            command.Parameters.Clear();
            command.Parameters.AddWithValue("@user_id", result);
            command.CommandText = "SELECT [key] FROM [AccountActivation] WHERE [id]=@user_id";
            try
            {
                using (SqlDataReader rdr = command.ExecuteReader())
                {
                    return result != -1 && !rdr.HasRows;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking user auth params (activation key)");
            }
        }

        public User LoadUser(string login)
        {
            return LoadUser(GetUserID(login));
        }

        public bool UserExists(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = "SELECT [id] FROM [Users] WHERE [id]=@id";
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    return reader.HasRows;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking user existence");
            }
        }

        public bool UserExists(string login)
        {
            return (GetUserID(login) != -1);
        }

        public int GetUserID(string login)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@login", login);
            command.CommandText = "SELECT [id] FROM [Users] WHERE [login]=@login";
            try
            {
                var res = command.ExecuteScalar().ToString();
                int result = -1;
                int.TryParse(res, out result);
                return result;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading user id");
            }
        }

        public void ModifyUser(User src)
        {
            var old_user = LoadUser(src.ID);
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@login", src.Login);
            command.Parameters.AddWithValue("@password", src.Password);
            command.Parameters.AddWithValue("@email", src.Email);
            command.Parameters.AddWithValue("@lastquota", src.LastQuota);
            command.CommandText = "UPDATE [Users] SET [login]=@login, [password]=@password, [email]=@email, [last_quota]=@lastquota WHERE [id]=@id";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while modifying user");
            }

            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            foreach (var file_Item in old_user.HostedFiles)
            {
                hfdao.UnlinkHostedFile(file_Item);
            }
            foreach (var file_item in src.HostedFiles)
            {
                hfdao.LinkHostedFile(file_item, src.ID);
            }


            IRoleDAO rdao = new SQL_Role_DAO();
            foreach (var role_item in old_user.Roles)
            {
                rdao.RemoveRoleFromUser(role_item.ID, src.ID);
            }
            foreach (var role_item in src.Roles)
            {
                rdao.AddRoleToUser(role_item.ID, src.ID);
            }

        }

        public void RemoveUser(User src)
        {
            // Removing roles
            IRoleDAO rdao = new SQL_Role_DAO();
            var user_roles = rdao.GetRolesForUser(src.ID);
            foreach (var roleItem in user_roles)
            {
                rdao.RemoveRoleFromUser(roleItem.ID, src.ID);
            }

            // HACK: We cannot directly access FileDAO, so stored files will last on hdd until we found them not connected to HostedFiles DB and remove

            // Removing hosted files
            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            var user_files = hfdao.GetHostedFilesForUser(src.ID);
            foreach (var hfileItem in user_files)
            {
                hfdao.UnlinkHostedFile(hfileItem);
            }

            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", src.ID);
            command.CommandText = "DELETE FROM [Users] WHERE [id]=@id";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while removing user");
            }
            return;
        }

        public int InsertUser(User src)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@login", src.Login);
            command.Parameters.AddWithValue("@password", src.Password);
            command.Parameters.AddWithValue("@email", src.Email);
            command.Parameters.AddWithValue("@lastquota", src.LastQuota);
            command.CommandText = "INSERT INTO [Users] ([login], [password], [email], [last_quota]) VALUES(@login, @password, @email, @lastquota)";
            int res = -1;
            try
            {
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.CommandText = "SELECT SCOPE_IDENTITY()";
                var result = command.ExecuteScalar().ToString();
                if (!int.TryParse(result, out res))
                {
                    return -1;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while adding user");
            }

            if (src.AccountKey != null)
            {
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@user_id", res);
                command.Parameters.AddWithValue("@user_key", src.AccountKey);
                command.CommandText = "INSERT INTO [AccountActivation] ([user_id], [user_key]) VALUES(@user_id, @user_key)";
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    throw new DAOException("Error occured while writing account key");
                }
            }

            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            foreach (var file_Item in src.HostedFiles)
            {
                hfdao.LinkHostedFile(file_Item, res);
            }

            IRoleDAO rdao = new SQL_Role_DAO();
            foreach (var role_item in src.Roles)
            {
                rdao.AddRoleToUser(role_item.ID, src.ID);
            }

            return res;
        }
    }
}
